package com.kwdb.importtool.util;

import com.kwdb.importtool.config.DatabaseConfig;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.Executor;

/**
 * 数据库连接工具类
 * 提供KWDB和MySQL数据库连接管理功能
 */
@Slf4j
public class DBConnection {

    // 连接池缓存
    private static final Map<String, Connection> connectionCache = new ConcurrentHashMap<>();
    private static final Map<String, Long> connectionTimestamp = new ConcurrentHashMap<>();

    // 连接超时设置（毫秒）
    private static final int CONNECTION_TIMEOUT = 30000;
    private static final int VALIDATION_TIMEOUT = 5000;
    private static final long MAX_CONNECTION_AGE = 30 * 60 * 1000; // 30分钟

    // 驱动程序类名
    private static final String KWDB_DRIVER_CLASS = "com.kaiwudb.Driver";
    private static final String MYSQL_DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";

    static {
        // 注册数据库驱动程序
        registerDrivers();
    }

    /**
     * 注册数据库驱动程序
     */
    private static void registerDrivers() {
        try {
            // 注册KWDB驱动
            Class.forName(KWDB_DRIVER_CLASS);
            log.info("KWDB驱动程序注册成功");
        } catch (ClassNotFoundException e) {
            log.warn("KWDB驱动程序未找到，请确保kaiwudb-jdbc依赖已添加");
        }

        try {
            // 注册MySQL驱动
            Class.forName(MYSQL_DRIVER_CLASS);
            log.info("MySQL驱动程序注册成功");
        } catch (ClassNotFoundException e) {
            log.warn("MySQL驱动程序未找到，请确保mysql-connector-java依赖已添加");
        }
    }

    /**
     * 获取KWDB数据库连接
     */
    public static Connection getKWDBConnection(DatabaseConfig config) throws SQLException {
        return getKWDBConnection(config.getKwdbUrl(), config.getKwdbUsername(), config.getKwdbPassword());
    }

    /**
     * 获取KWDB数据库连接
     */
    public static Connection getKWDBConnection(String url, String username, String password) throws SQLException {
        String connectionKey = "kwdb:" + url + ":" + username;

        // 检查缓存中是否有可用的连接
        Connection cachedConnection = getCachedConnection(connectionKey);
        if (cachedConnection != null) {
            return cachedConnection;
        }

        // 创建新连接
        log.debug("创建新的KWDB连接: {}", url);

        Properties props = new Properties();
        props.setProperty("user", username);
        props.setProperty("password", password != null ? password : "");
        props.setProperty("connectTimeout", String.valueOf(CONNECTION_TIMEOUT));
        props.setProperty("socketTimeout", String.valueOf(CONNECTION_TIMEOUT));

        try {
            Connection connection = DriverManager.getConnection(url, props);
            connection.setAutoCommit(true);

            // 缓存连接
            cacheConnection(connectionKey, connection);

            log.info("KWDB连接创建成功: {}", url);
            return connection;

        } catch (SQLException e) {
            log.error("KWDB连接创建失败: {}", url, e);
            throw new SQLException("无法连接到KWDB: " + e.getMessage(), e);
        }
    }

    /**
     * 获取MySQL数据库连接
     */
    public static Connection getMySQLConnection(DatabaseConfig config) throws SQLException {
        return getMySQLConnection(config.getMysqlUrl(), config.getMysqlUsername(), config.getMysqlPassword());
    }

    /**
     * 获取MySQL数据库连接
     */
    public static Connection getMySQLConnection(String url, String username, String password) throws SQLException {
        String connectionKey = "mysql:" + url + ":" + username;

        // 检查缓存中是否有可用的连接
        Connection cachedConnection = getCachedConnection(connectionKey);
        if (cachedConnection != null) {
            return cachedConnection;
        }

        // 创建新连接
        log.debug("创建新的MySQL连接: {}", url);

        Properties props = new Properties();
        props.setProperty("user", username);
        props.setProperty("password", password != null ? password : "");
        props.setProperty("connectTimeout", String.valueOf(CONNECTION_TIMEOUT));
        props.setProperty("socketTimeout", String.valueOf(CONNECTION_TIMEOUT));
        props.setProperty("useSSL", "false");
        props.setProperty("allowPublicKeyRetrieval", "true");
        props.setProperty("serverTimezone", "UTC");
        props.setProperty("characterEncoding", "UTF-8");

        try {
            Connection connection = DriverManager.getConnection(url, props);
            connection.setAutoCommit(true);

            // 缓存连接
            cacheConnection(connectionKey, connection);

            log.info("MySQL连接创建成功: {}", url);
            return connection;

        } catch (SQLException e) {
            log.error("MySQL连接创建失败: {}", url, e);
            throw new SQLException("无法连接到MySQL: " + e.getMessage(), e);
        }
    }

    /**
     * 从缓存获取连接
     */
    private static Connection getCachedConnection(String connectionKey) {
        Connection connection = connectionCache.get(connectionKey);
        if (connection != null) {
            try {
                // 检查连接是否仍然有效
                if (isConnectionValid(connection)) {
                    // 更新连接时间戳
                    connectionTimestamp.put(connectionKey, System.currentTimeMillis());
                    log.debug("使用缓存的数据库连接: {}", connectionKey);
                    return connection;
                } else {
                    // 连接无效，从缓存中移除
                    log.debug("缓存连接已失效: {}", connectionKey);
                    closeConnectionSilently(connection);
                    connectionCache.remove(connectionKey);
                    connectionTimestamp.remove(connectionKey);
                }
            } catch (Exception e) {
                log.warn("检查连接有效性时出错: {}", connectionKey, e);
                closeConnectionSilently(connection);
                connectionCache.remove(connectionKey);
                connectionTimestamp.remove(connectionKey);
            }
        }
        return null;
    }

    /**
     * 缓存连接
     */
    private static void cacheConnection(String connectionKey, Connection connection) {
        connectionCache.put(connectionKey, connection);
        connectionTimestamp.put(connectionKey, System.currentTimeMillis());
        log.debug("数据库连接已缓存: {}", connectionKey);
    }

    /**
     * 检查连接是否有效
     */
    public static boolean isConnectionValid(Connection connection) {
        if (connection == null) {
            return false;
        }

        try {
            return connection.isValid(VALIDATION_TIMEOUT / 1000);
        } catch (SQLException e) {
            log.debug("连接有效性检查失败", e);
            return false;
        }
    }

    /**
     * 测试KWDB连接
     */
    public static boolean testKWDBConnection(DatabaseConfig config) {
        return testKWDBConnection(config.getKwdbUrl(), config.getKwdbUsername(), config.getKwdbPassword());
    }

    /**
     * 测试KWDB连接
     */
    public static boolean testKWDBConnection(String url, String username, String password) {
        Connection connection = null;
        try {
            connection = getKWDBConnection(url, username, password);

            // 执行简单查询测试
            try (Statement stmt = connection.createStatement()) {
                stmt.setQueryTimeout(10);
                try (ResultSet rs = stmt.executeQuery("SELECT 1")) {
                    if (rs.next()) {
                        log.info("KWDB连接测试成功: {}", url);
                        return true;
                    }
                }
            }

            return false;

        } catch (SQLException e) {
            log.error("KWDB连接测试失败: {}", url, e);
            return false;
        } finally {
            closeConnectionSilently(connection);
        }
    }

    /**
     * 测试MySQL连接
     */
    public static boolean testMySQLConnection(DatabaseConfig config) {
        return testMySQLConnection(config.getMysqlUrl(), config.getMysqlUsername(), config.getMysqlPassword());
    }

    /**
     * 测试MySQL连接
     */
    public static boolean testMySQLConnection(String url, String username, String password) {
        Connection connection = null;
        try {
            connection = getMySQLConnection(url, username, password);

            // 执行简单查询测试
            try (Statement stmt = connection.createStatement()) {
                stmt.setQueryTimeout(10);
                try (ResultSet rs = stmt.executeQuery("SELECT 1")) {
                    if (rs.next()) {
                        log.info("MySQL连接测试成功: {}", url);
                        return true;
                    }
                }
            }

            return false;

        } catch (SQLException e) {
            log.error("MySQL连接测试失败: {}", url, e);
            return false;
        } finally {
            closeConnectionSilently(connection);
        }
    }

    /**
     * 执行SQL查询
     */
    public static ResultSet executeQuery(Connection connection, String sql) throws SQLException {
        return executeQuery(connection, sql, null);
    }

    /**
     * 执行SQL查询
     */
    public static ResultSet executeQuery(Connection connection, String sql, Integer timeout) throws SQLException {
        if (connection == null) {
            throw new SQLException("数据库连接为空");
        }

        Statement stmt = connection.createStatement();
        if (timeout != null) {
            stmt.setQueryTimeout(timeout);
        }

        return stmt.executeQuery(sql);
    }

    /**
     * 执行SQL更新
     */
    public static int executeUpdate(Connection connection, String sql) throws SQLException {
        return executeUpdate(connection, sql, null);
    }

    /**
     * 执行SQL更新
     */
    public static int executeUpdate(Connection connection, String sql, Integer timeout) throws SQLException {
        if (connection == null) {
            throw new SQLException("数据库连接为空");
        }

        try (Statement stmt = connection.createStatement()) {
            if (timeout != null) {
                stmt.setQueryTimeout(timeout);
            }
            return stmt.executeUpdate(sql);
        }
    }

    /**
     * 执行批量更新
     */
    public static int[] executeBatch(Connection connection, java.util.List<String> sqlList) throws SQLException {
        return executeBatch(connection, sqlList, null);
    }

    /**
     * 执行批量更新
     */
    public static int[] executeBatch(Connection connection, java.util.List<String> sqlList, Integer timeout) throws SQLException {
        if (connection == null) {
            throw new SQLException("数据库连接为空");
        }

        try (Statement stmt = connection.createStatement()) {
            if (timeout != null) {
                stmt.setQueryTimeout(timeout);
            }

            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }

            return stmt.executeBatch();
        }
    }

    /**
     * 关闭数据库连接
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                if (!connection.isClosed()) {
                    connection.close();
                    log.debug("数据库连接已关闭");
                }
            } catch (SQLException e) {
                log.warn("关闭数据库连接时出错", e);
            }
        }
    }

    /**
     * 安静地关闭连接（不抛出异常）
     */
    public static void closeConnectionSilently(Connection connection) {
        try {
            closeConnection(connection);
        } catch (Exception e) {
            // 忽略异常
            log.debug("关闭连接时发生异常（已忽略）", e);
        }
    }

    /**
     * 关闭结果集
     */
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.warn("关闭结果集时出错", e);
            }
        }
    }

    /**
     * 关闭语句
     */
    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                log.warn("关闭语句时出错", e);
            }
        }
    }

    /**
     * 关闭所有资源
     */
    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        closeResultSet(rs);
        closeStatement(stmt);
        closeConnection(conn);
    }

    /**
     * 清理过期的连接缓存
     */
    public static void cleanupExpiredConnections() {
        long currentTime = System.currentTimeMillis();
        int cleanedCount = 0;

        for (Map.Entry<String, Long> entry : connectionTimestamp.entrySet()) {
            String key = entry.getKey();
            long timestamp = entry.getValue();

            if (currentTime - timestamp > MAX_CONNECTION_AGE) {
                Connection connection = connectionCache.remove(key);
                connectionTimestamp.remove(key);

                if (connection != null) {
                    closeConnectionSilently(connection);
                    cleanedCount++;
                }
            }
        }

        if (cleanedCount > 0) {
            log.info("清理了 {} 个过期的数据库连接", cleanedCount);
        }
    }

    /**
     * 清除所有连接缓存
     */
    public static void clearConnectionCache() {
        int count = connectionCache.size();

        for (Connection connection : connectionCache.values()) {
            closeConnectionSilently(connection);
        }

        connectionCache.clear();
        connectionTimestamp.clear();

        log.info("清除了 {} 个数据库连接缓存", count);
    }

    /**
     * 获取数据库元数据
     */
    public static DatabaseMetaData getDatabaseMetaData(Connection connection) throws SQLException {
        if (connection == null) {
            throw new SQLException("数据库连接为空");
        }
        return connection.getMetaData();
    }

    /**
     * 获取数据库产品名称和版本
     */
    public static String getDatabaseInfo(Connection connection) {
        try {
            DatabaseMetaData metaData = getDatabaseMetaData(connection);
            return metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion();
        } catch (SQLException e) {
            log.warn("获取数据库信息失败", e);
            return "未知数据库";
        }
    }

    /**
     * 检查表是否存在
     */
    public static boolean tableExists(Connection connection, String tableName) throws SQLException {
        return tableExists(connection, null, null, tableName);
    }

    /**
     * 检查表是否存在
     */
    public static boolean tableExists(Connection connection, String catalog, String schema, String tableName) throws SQLException {
        if (connection == null || tableName == null) {
            return false;
        }

        try (ResultSet rs = connection.getMetaData().getTables(catalog, schema, tableName, new String[]{"TABLE"})) {
            return rs.next();
        }
    }

    /**
     * 获取表行数
     */
    public static long getTableRowCount(Connection connection, String tableName) throws SQLException {
        if (connection == null || tableName == null) {
            return 0;
        }

        String sql = "SELECT COUNT(*) FROM " + tableName;
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    /**
     * 在事务中执行操作
     */
    public static void executeInTransaction(Connection connection, TransactionCallback callback) throws SQLException {
        if (connection == null) {
            throw new SQLException("数据库连接为空");
        }

        boolean originalAutoCommit = connection.getAutoCommit();
        try {
            connection.setAutoCommit(false);
            callback.execute(connection);
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(originalAutoCommit);
        }
    }

    /**
     * 事务回调接口
     */
    @FunctionalInterface
    public interface TransactionCallback {
        void execute(Connection connection) throws SQLException;
    }

    /**
     * 连接信息类
     */
    public static class ConnectionInfo {
        private final String url;
        private final String username;
        private final String databaseProduct;
        private final boolean isValid;

        public ConnectionInfo(String url, String username, String databaseProduct, boolean isValid) {
            this.url = url;
            this.username = username;
            this.databaseProduct = databaseProduct;
            this.isValid = isValid;
        }

        // Getters
        public String getUrl() { return url; }
        public String getUsername() { return username; }
        public String getDatabaseProduct() { return databaseProduct; }
        public boolean isValid() { return isValid; }

        @Override
        public String toString() {
            return String.format("ConnectionInfo{url='%s', username='%s', product='%s', valid=%s}",
                    url, username, databaseProduct, isValid);
        }
    }

    /**
     * 获取连接信息
     */
    public static ConnectionInfo getConnectionInfo(Connection connection) {
        if (connection == null) {
            return new ConnectionInfo(null, null, "无连接", false);
        }

        try {
            DatabaseMetaData metaData = connection.getMetaData();
            String url = metaData.getURL();
            String username = metaData.getUserName();
            String product = metaData.getDatabaseProductName();
            boolean valid = connection.isValid(5);

            return new ConnectionInfo(url, username, product, valid);

        } catch (SQLException e) {
            return new ConnectionInfo(null, null, "错误: " + e.getMessage(), false);
        }
    }
}